package com.spring.housing1.server;

import antlr.StringUtils;
import com.spring.housing1.constant.Constants;
import com.spring.housing1.constant.isNull;
import com.spring.housing1.other.FileUpload;
import com.spring.housing1.returnType.CommenPageType;
import com.spring.housing1.returnType.CommenResult;
import org.apache.commons.net.ftp.FTPClient;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

@Service
public class LoginServe {



    private final Logger log = LoggerFactory.getLogger(LoginServe.class);
    @Qualifier("Db1Template")
    @Autowired
    JdbcTemplate jdbcTemplate;

    public CommenResult<Object> doLogin( Map<String, String> param, HttpSession httpSession) throws Exception {

        String q_sql ="select  count(*) from web_mana_user where user_name = ? and pass_word = ? and enable =1";
              if(param.get("user_name")==null || param.get("pass_word")==null){
                  throw new NullPointerException("参数不全!");
              }
              String user =param.get("user_name");
              String pass =param.get("pass_word");
//             判断数据库是否有该用户存在
            Map<String, Object> maps = jdbcTemplate.queryForMap(q_sql,user,pass);
//            没该有用户return
            if(Integer.parseInt(maps.get("count(*)").toString())==0){
                return new CommenResult<>(201,"账号或密码错误!","空");
        }
            else{
                //设置token到session里
                String uu_token = UUID.randomUUID().toString();
                httpSession.setAttribute(httpSession.getId(),uu_token);
                Map m = new ConcurrentHashMap();
                m.put("token",uu_token);
//返回当前用户id
                m.put("id",jdbcTemplate.queryForMap("select id from web_mana_user where user_name = ? and pass_word = ?",user,pass).get("id").toString());
                m.put("room_id",jdbcTemplate.queryForMap("select room_id from web_mana_user where user_name = ? and pass_word = ?",user,pass).get("room_id").toString());
                return  new CommenResult<>(200,"登录成功!",m);
            }

    }



    public CommenResult<Object> queryRoleDoctor(Map<String, String> param) {
        String sql = "select a.* from \n" +
                "(select a.id ,a.name,a.user_name,a.real_name,a.cell_phone,a.dim_time,a.enable,b.room,b.room_id,a.pass_word from \n" +
                "(select id ,name,real_name,user_name,cell_phone,dim_time,enable,room_id,pass_word  from  web_mana_user) a\n" +
                "left join room  b on\n" +
                "b.room_id = a.room_id  )a where 1=1 ";
        List params = new ArrayList<String>();
        if(param.get("id")!=null && !param.get("id").equals("")){
            sql +=" and id = ? ";
            params.add(param.get("id"));
        }
        if(param.get("real_name")!="" && !param.get("real_name").equals("")){
            sql +=" and real_name like concat('%',?,'%') ";
            params.add(param.get("real_name"));
        }
        if(param.get("room_id")!=null && !param.get("room_id").equals("")){
            sql +=" and room_id = ? ";
            params.add(param.get("room_id"));
        }
        if(param.get("user_name")!=null && !param.get("user_name").equals("")){
            sql +=" and user_name like concat('%',?,'%') ";
            params.add(param.get("user_name"));
        }
        if(param.get("cell_phone")!=null  && !param.get("cell_phone").equals("")){
            sql +=" and cell_phone  like concat('%',?,'%') ";
            params.add(param.get("cell_phone"));
        }
        if(param.get("enable")!=null && !param.get("enable").equals("")){
            sql +=" and enable = ? ";
            params.add(param.get("enable"));
        }
        String ssl2 = sql;
        sql +=("limit "+((Integer.parseInt(Constants.pageNum)-1)*(Integer.parseInt(Constants.pageSize))+","+Constants.pageSize));
        log.info("{执行sql为:{}",sql);
//        总数
        List<Map<String, Object>> stringObjectMap = jdbcTemplate.queryForList(ssl2,params.toArray());
//        分页后数据
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, params.toArray());
        Map<String ,Object> res = new HashMap();
        res.put("data",maps);
        res.put("total",stringObjectMap.size());
        return new CommenResult<>(200,"查询医生信息成功!",res);
    }

    public CommenResult<Object> queryRoom(Map<String, String> param) {

        String sql ="select room_id,room,descs,enable,create_at from room where 1=1 ";
        List arr = new ArrayList();
        if(param.get("room")!=null  && !param.get("room").equals("")){
            sql +=" and   room  like concat('%',?,'%')";
            arr.add(param.get("room"));
        }
        if(param.get("enable")!=null && !param.get("enable").equals("")){
            sql +=" and enable =? ";
            arr.add(param.get("enable"));
        }
        String ssl2 = sql;
        sql +=("limit "+((Integer.parseInt(Constants.pageNum)-1)*(Integer.parseInt(Constants.pageSize))+","+Constants.pageSize));
//        总数
        List<Map<String, Object>> stringObjectMap = jdbcTemplate.queryForList(ssl2,arr.toArray());
//        分页后数据
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, arr.toArray());
        Map<String ,Object> res = new HashMap();
        res.put("data",maps);
        res.put("total",stringObjectMap.size());
        return new CommenResult<>(200,"查询科室信息成功!",res);
    }

    public CommenResult<Object> queryLoginUserInfo(Map<String, String> param) {
        String sql = "select a.* from \n" +
                "(select a.id ,a.name,a.user_name,a.real_name,a.cell_phone,a.dim_time,a.enable,b.room,b.room_id from \n" +
                "(select id ,name,real_name,user_name,cell_phone,dim_time,enable,room_id  from  web_mana_user) a\n" +
                "left join room  b on\n" +
                "b.room_id = a.room_id and a.enable =1)a where  id =?";
        return new CommenResult<>(200,"查询用户信息成功!",jdbcTemplate.queryForMap(sql,param.get("id")));

    }

    public CommenResult<Object> orderQuery(Map<String, String> param) {

        String sql ="select a.* from (select a.*,COALESCE(b.real_name,\"\") doctor2_name from \n" +
                "(select a.patient,a.cell_phone,a.remark,a.apply_time,a.enable,a.doctor_id,a.node_id,a.open_id,a.doctor2_id,a.cd_result,a.deal_result ,a.img_path ,\n" +
                "case when b.real_name is null then \"\" else b.real_name end as doctor_name\n" +
                "from ctp_aff  a\n" +
                "left join \n" +
                "\n" +
                "(select real_name,id from web_mana_user) b\n" +
                "\n" +
                "on a.doctor_id = b.id) a left join (select real_name,id from web_mana_user) b\n" +
                "\n" +
                "on a.doctor2_id = b.id)a  where 1=1 ";
        List params = new ArrayList();
        if(param.get("patient")!=null && !param.get("patient").equals("")){
            sql +=" and patient like concat('%',?,'%') ";
            params.add(param.get("patient"));
        }
        if(param.get("doctor_name")!=null && !param.get("doctor_name").equals("")){
            sql +=" and doctor_name like concat('%',?,'%') ";
            params.add(param.get("doctor_name"));
        }
        if(param.get("doctor2_name")!=null && !param.get("doctor2_name").equals("")){
            sql +=" and doctor2_name like concat('%',?,'%') ";
            params.add(param.get("doctor2_name"));
        }
        if(param.get("cell_phone")!=null && !param.get("cell_phone").equals("")){
            sql +=" and cell_phone like concat('%',?,'%') ";
            params.add(param.get("cell_phone"));
        }
        if(param.get("enable")!=null && !param.get("enable").equals("")){
            sql +=" and enable = ? ";
            params.add(param.get("enable"));
        }
        if(param.get("remark")!=null && !param.get("remark").equals("")){
            sql +=" and remark like concat('%',?,'%') ";
            params.add(param.get("remark"));
        }
        String count ="select count(*) from ( "+sql+" )a";
        sql +=("limit "+((Integer.parseInt(Constants.pageNum)-1)*(Integer.parseInt(Constants.pageSize))+","+Constants.pageSize));
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, params.toArray());
        Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(count, params.toArray());
//        FTPClient ftp = new FTPClient();
//        ftp.setAutodetectUTF8(true);
//        ftp.setControlEncoding("UTF-8");
//        FileUpload.login(ftp);
//        List<Map<String, Object>> collect = null;
//        try {
//        collect = maps.stream().map(res -> {
////            当前病单对应的文件地址
//            String path = ("/" + res.get("open_id") + "/" + res.get("node_id") + "/");
////            获取所有影像地址
//            String path1 = null;
//
//            try {
//                path1 = FileUpload.isPath(path,log,ftp);
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//
////            格式化地址输出
//            String resPath = path1.equals("")?"":path1.substring(1);
//                res.put("img_path", resPath);
//            return res;
//        }).collect(Collectors.toList());
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        finally {
//            if (ftp.isConnected()) {
//                try {
//                    ftp.disconnect();
//                } catch (IOException ioe) {
//                }
//            }
//        }
        Map<String ,Object> ress = new HashMap();
        ress.put("data",maps);
        ress.put("total",stringObjectMap.get("count(*)"));

        return new CommenResult<>(200,"待办查询成功!",ress);
    }


    public CommenResult<Object> myOrderQuerys(Map<String, String> param) {


        String sql ="select a.* from (select a.*,COALESCE(b.real_name,\"\") doctor2_name from \n" +
                "(select a.patient,a.cell_phone,a.remark,a.apply_time,a.enable,a.doctor_id,a.node_id,a.open_id,a.doctor2_id,a.cd_result,a.deal_result ,a.img_path,\n" +
                "case when b.real_name is null then \"\" else b.real_name end as doctor_name\n" +
                "from ctp_aff  a\n" +
                "left join \n" +
                "\n" +
                "(select real_name,id from web_mana_user) b\n" +
                "\n" +
                "on a.doctor_id = b.id) a left join (select real_name,id from web_mana_user) b\n" +
                "\n" +
                "on a.doctor2_id = b.id)a  where 1=1 ";
        List params = new ArrayList();
        if(param.get("patient")!=null && !param.get("patient").equals("")){
            sql +=" and patient like concat('%',?,'%') ";
            params.add(param.get("patient"));
        }

        if(param.get("cell_phone")!=null && !param.get("cell_phone").equals("")){
            sql +=" and cell_phone like concat('%',?,'%') ";
            params.add(param.get("cell_phone"));
        }

        if(param.get("remark")!=null && !param.get("remark").equals("")){
            sql +=" and remark like concat('%',?,'%') ";
            params.add(param.get("remark"));
        }

        sql +=(" and  enable = "+(param.get("doctor_type").equals("2")?0:4)+" ");
//2为影像医生（doctor_id），doctor2_id为内外科医生
        sql +=  (param.get("doctor_type").equals("2")? " and  doctor_id  = ? ":" and  doctor2_id  = ? ");
        params.add((param.get("doctor_type").equals("2")? param.get("doctor_id"):param.get("doctor2_id")));

        String count ="select count(*) from ( "+sql+" )a";
        sql +=("limit "+((Integer.parseInt(Constants.pageNum)-1)*(Integer.parseInt(Constants.pageSize))+","+Constants.pageSize));
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, params.toArray());
        Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(count, params.toArray());
//        FTPClient ftp = new FTPClient();
//        ftp.setAutodetectUTF8(true);
//        ftp.setControlEncoding("UTF-8");
//        FileUpload.login(ftp);
//        List<Map<String, Object>> collect = null;
//        try {
//            collect = maps.stream().map(res -> {
////            当前病单对应的文件地址
//                String path = ("/" + res.get("open_id") + "/" + res.get("node_id") + "/");
////            获取所有影像地址
//                String path1 = null;
//
//                try {
//                    path1 = FileUpload.isPath(path,log,ftp);
//                } catch (IOException e) {
//                    e.printStackTrace();
//                }
//
////            格式化地址输出
//                String resPath = path1.equals("")?"":path1.substring(1);
//                res.put("img_path", resPath);
//                return res;
//            }).collect(Collectors.toList());
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        finally {
//            if (ftp.isConnected()) {
//                try {
//                    ftp.disconnect();
//                } catch (IOException ioe) {
//                }
//            }
//        }
        Map<String ,Object> ress = new HashMap();
        ress.put("data",maps);
        ress.put("total",stringObjectMap.get("count(*)"));

        return new CommenResult<>(200,"待办查询成功!",ress);
    }

    public CommenResult<Object> showIndex() {
//第一部分数据
        List<Map<String, Object>> res1 = jdbcTemplate.queryForList("-- 当天\n" +
                "select \n" +
                "sum(case when DATE_FORMAT(apply_time,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') and (enable =1 or enable = 2 ) then 1 else 0 end ) handleNum,\n" +
                "sum(case when  DATE_FORMAT(apply_time,'%Y-%m-%d') = DATE_FORMAT(now(),'%Y-%m-%d') then 1 else 0 end ) applyNum,'今日' tag \n" +
                " from ctp_aff where enable !=3  \n" +
                " union all \n" +
                " -- 本周\n" +
                " select \n" +
                "sum(case when week(apply_time) = week(now()) and (enable =1 or enable = 2 ) then 1 else 0 end ) handleNum,\n" +
                "sum(case when  week(apply_time) = week(now()) then 1 else 0 end ) applyNum,'本周' tag \n" +
                " from ctp_aff  where enable !=3 \n" +
                "  union all \n" +
                " -- 本月\n" +
                "select \n" +
                "sum(case when DATE_FORMAT(apply_time,'%Y-%m') = DATE_FORMAT(now(),'%Y-%m') and (enable =1 or enable = 2 ) then 1 else 0 end ) handleNum,\n" +
                "sum(case when  DATE_FORMAT(apply_time,'%Y-%m') = DATE_FORMAT(now(),'%Y-%m') then 1 else 0 end ) applyNum,'本月' tag \n" +
                " from ctp_aff  where enable !=3 ");
        List<Map<String, Object>> res2 = jdbcTemplate.queryForList("\n" +
                " SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 13 day) as dates ,count(*) num\n" +
                " from ctp_aff where enable !=3 and  DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 13 day)\n" +
                " union all \n" +
                " SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 12 day) as dates ,count(*) num\n" +
                " from ctp_aff where  enable !=3 and DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 12 day)\n" +
                "  union all \n" +
                "  SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 11 day) as dates ,count(*) num\n" +
                " from ctp_aff where enable !=3 and   DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 11 day) union all \n" +
                "  SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 10 day) as dates ,count(*) num\n" +
                " from ctp_aff where  enable !=3 and  DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 10 day) union all \n" +
                "  SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 9 day) as dates ,count(*) num\n" +
                " from ctp_aff where  enable !=3 and  DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 9 day) union all \n" +
                "  SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 8 day) as dates ,count(*) num\n" +
                " from ctp_aff where enable !=3 and   DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 8 day) union all \n" +
                "  SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 7 day) as dates ,count(*) num\n" +
                " from ctp_aff where enable !=3 and  DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 7 day) union all \n" +
                "  SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 6 day) as dates ,count(*) num\n" +
                " from ctp_aff where  enable !=3 and  DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 6 day) union all \n" +
                "  SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 5 day) as dates ,count(*) num\n" +
                " from ctp_aff where enable !=3 and  DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 5 day) union all \n" +
                "   SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 4 day) as dates ,count(*) num\n" +
                " from ctp_aff where enable !=3 and   DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 4 day) union all \n" +
                "   SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 3 day) as dates ,count(*) num\n" +
                " from ctp_aff where  enable !=3 and  DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 3 day) union all \n" +
                "   SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 2 day) as dates ,count(*) num\n" +
                " from ctp_aff where  enable !=3 and  DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 2 day) union all \n" +
                "   SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 1 day) as dates ,count(*) num\n" +
                " from ctp_aff where enable !=3 and  DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 1 day) union all \n" +
                "   SELECT   date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 0 day) as dates ,count(*) num\n" +
                " from ctp_aff where enable !=3 and  DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval 0 day)");
        List res = new ArrayList<>();
        res.add(res1);
        res.add(res2);
        return  new CommenResult<>(200,"查询成功!",res);
    }

    public CommenResult<Object>  showSelfindex(Map<String,String> param) {
        String enable = param.get("doctor_type").equals("2")?"(2,1,4,5)":"(1)";
        String doctor_idty = param.get("doctor_type").equals("2")?"doctor_id":"doctor2_id";
        String room_id = param.get("doctor_type");
        String doctor_id = param.get("doctor_id");
        String sql_1 ="select a.*,round(myCompleteNum/doctorNum,2) as rates from (select date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval  ";
        String sql_2="  day ) dates,\n" +
                "sum(case when DATE_FORMAT(apply_time,'%Y-%m-%d') = date_sub(DATE_FORMAT(now(),'%Y-%m-%d'),interval  ";
        String sql_3 ="  day ) and enable in " +
                enable +
                " then 1 else 0 end ) myCompleteNum,\n" +
                "(select count(*) from web_mana_user where room_id ="+room_id+" and enable ="+1+")  doctorNum\n" +
                " from ctp_aff   WHERE "+doctor_idty+" = "+doctor_id +")a \n";

        String sql ="";
//        七天数据sql拼接
        for (int i =7;i>0;i--){
            if(i==7)
            sql +=(sql_1 +(7-i) +sql_2+(7-i) +sql_3);
            else{
                sql +=("union all "+sql_1 +(7-i) +sql_2+(7-i) +sql_3);
            }
        }
        log.info(sql);
        return new CommenResult<>(200,"查询成功!",jdbcTemplate.queryForList(sql));
    }
}
